Installation

To install use pip:

$ pip install ipysheet

To make it work for Jupyter lab:

$ jupyter labextension ipysheet

If you have notebook 5.2 or below, you also need to execute:

$ jupyter nbextension enable --py --sys-prefix ipysheet
$ jupyter nbextension enable --py --sys-prefix ipysheet.renderer_nbext

Getting started

Although ipysheet contains an object oriented interface, we recomment using the "state machine" based interface, similar to matplotlib's pyplot/pylab interface. Comparible to matplotlib pylab interface, this interface keeps track of the current sheet. Using the cell function, Cell widgets are added to the current sheet.

Importing ipysheet and invoking the sheet function will create the default spreadsheet widget. The function returns a Sheet instance, leaving that expression as a last statement of a code cell will display it, otherwise use display(sheet).

Note that this documentation is a Jupyter notebook, and you can try it out directly on Binder:



In [1]:

    
import ipysheet
sheet = ipysheet.sheet()
sheet

Using the cell function, we can create Cell widgets that are directly added to the current sheet.



In [2]:

    
sheet = ipysheet.sheet(rows=3, columns=4)
cell1 = ipysheet.cell(0, 0, 'Hello')
cell2 = ipysheet.cell(2, 0, 'World')
cell_value = ipysheet.cell(2,2, 42.)
sheet

Events

Using link or observe we can link widgets together, or attach event handlers

**Note:** The examples below contain event handler written in Python that needs a running kernel, they will not work in the pure html documentation. They do work in binder!



In [3]:

    
import ipywidgets as widgets
sheet = ipysheet.sheet(rows=3, columns=2, column_headers=False, row_headers=False)
cell_a = ipysheet.cell(0, 1, 1, label_left='a')
cell_b = ipysheet.cell(1, 1, 2, label_left='b')
cell_sum = ipysheet.cell(2, 1, 3, label_left='sum', read_only=True)

# create a slider linked to cell a
slider = widgets.FloatSlider(min=-10, max=10, description='a')
widgets.jslink((cell_a, 'value'), (slider, 'value'))

# changes in a or b should trigger this function
def calculate(change):
    cell_sum.value = cell_a.value + cell_b.value

cell_a.observe(calculate, 'value')
cell_b.observe(calculate, 'value')


widgets.VBox([sheet, slider])

Cell ranges

Instead of referring to a single cell, we can also refer to cell ranges, rows and columns.



In [4]:

    
sheet = ipysheet.sheet(rows=5, columns=4)
row = ipysheet.row(0, [0, 1, 2, 3], background_color="red")
column = ipysheet.column(1, ["a", "b", "c", "d"], row_start=1, background_color="green")
cells = ipysheet.cell_range([["hi", "ola"], ["ciao", "bonjour"], ["hallo", "guten tag"]],
                            row_start=1, column_start=2, background_color="yellow")
sheet

Calculations

Since this is such a common pattern, a helper decorator calculation is provided, shortening the above code considerably.



In [5]:

    
import ipywidgets as widgets
sheet = ipysheet.sheet(rows=3, columns=2, column_headers=False, row_headers=False)
cell_a = ipysheet.cell(0, 1, 1, label_left='a')
cell_b = ipysheet.cell(1, 1, 2, label_left='b')
cell_sum = ipysheet.cell(2, 1, 3, label_left='sum', read_only=True)

# create a slider linked to cell a
slider = widgets.FloatSlider(min=-10, max=10, description='a')
widgets.jslink((cell_a, 'value'), (slider, 'value'))

@ipysheet.calculation(inputs=[cell_a, cell_b], output=cell_sum)
def calculate(a, b):
    return a + b

widgets.VBox([sheet, slider])

Renderers

ipysheet is build on Handsontable, which allows custom renderers, which we also support. Note that this means ipysheet allows arbitrary JavaScript injection (TODO: make this part optional)



In [6]:

    
jscode_renderer_negative = """
function (instance, td, row, col, prop, value, cellProperties) {
    Handsontable.renderers.TextRenderer.apply(this, arguments);
    if (value < 0)
        td.style.backgroundColor = 'red'
    else
        td.style.backgroundColor = 'green'
}
"""
ipysheet.renderer(code=jscode_renderer_negative, name='negative');



In [7]:

    
import random
s = ipysheet.sheet(rows=3, columns=4)
data = [[random.randint(-10, 10) for j in range(4)] for j in range(3)]
ipysheet.cell_range(data, renderer='negative')
s

If flexx is installed, Python code can be transpiled to JavaScript at runtime.



In [8]:

    
def renderer_negative(instance, td, row, col, prop, value, cellProperties):
    Handsontable.renderers.TextRenderer.apply(this, arguments);
    if value < 0:
        td.style.backgroundColor = 'orange'
    else:
        td.style.backgroundColor = ''
ipysheet.renderer(code=renderer_negative, name='negative_transpiled');



In [9]:

    
import random
s = ipysheet.sheet(rows=3, columns=4)
data = [[random.randint(-10, 10) for j in range(4)] for j in range(3)]
ipysheet.cell_range(data, renderer='negative_transpiled')
s